﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using Column = MvvmOrm.Models.Column.Row;
using DatabaseModel = MvvmOrm.Models.DatabaseModel.Row;

namespace MvvmOrm.DbmsModules
{
    sealed class SqliteModule : DbmsModuleBase, IDbmsModule
    {
        #region singleton

        private static readonly SqliteModule instance = new SqliteModule();

        private SqliteModule() { }

        public static SqliteModule Instance
        {
            get
            {
                return instance;
            }
        }

        #endregion

        #region private

        SQLiteConnection _conn;

        static Column MapColumnInfo(DataRow row, bool isView)
        {
            Column column = new Column();

            if (!isView)
                column.Name = row["COLUMN_NAME"] as string;
            else
                column.Name = row["VIEW_COLUMN_NAME"] as string;

            column.MaxLength = (int)row["CHARACTER_MAXIMUM_LENGTH"];
            column.IsPrimaryKey = (bool)row["PRIMARY_KEY"];
            column.DbType = (int)GetColumnDbType(row["DATA_TYPE"] as string, (int)row["CHARACTER_MAXIMUM_LENGTH"]);
            column.IsAutoIncrement = (bool)row["AUTOINCREMENT"];
            column.IsReadOnly = column.IsAutoIncrement;
            column.Nullable = (bool)row["IS_NULLABLE"];
            return column;
        }

        static DbType GetColumnDbType(string type, int length)
        {
            if (type == null)
                return DbType.Object;
            switch (type.ToLowerInvariant()) {
            case "tinyint":
            case "smallint":
            case "int":
            case "integer":
                switch (length) {
                case 2:
                    return DbType.Int16;
                case 4:
                    return DbType.Int32;
                case 8:
                default:
                    return DbType.Int64;
                }
            case "string":
            case "varchar":
            case "nvarchar":
            case "text":
                return DbType.String;
            case "bool":
            case "boolean":
                return DbType.Boolean;
            case "binary":
            case "blob":
            default:
                return DbType.Binary;
            }
        }

        #endregion

        #region IDbmsModule

        public IEnumerable<string> AdditionalReferencedAssemblies
        {
            get { return new[] { Assembly.GetAssembly(typeof(System.Data.SQLite.SQLiteConnection)).Location }; }
        }

        #endregion

        #region DbmsModuleBase

        protected override string GetDbTypeStringed(Column c)
        {
            return ((DbType)c.DbType).ToString();
        }

        public override void Connect(DatabaseModel model)
        {
            if (!model.UseSchemaFile) {
                if (String.IsNullOrWhiteSpace(model.Database))
                    throw new Exception("no database specified");
                if (String.IsNullOrWhiteSpace(model.Password))
                    _conn = new SQLiteConnection("Data Source=" + model.Database + ";");
                else
                    _conn = new SQLiteConnection("Data Source=" + model.Database + ";" + "Password=" + model.Password + ";");
                _conn.Open();
            }
            else {
                if (String.IsNullOrWhiteSpace(model.SchemaFile))
                    throw new Exception("no schema file specified");
                if (!File.Exists(model.SchemaFile))
                    throw new Exception("schema file [" + model.SchemaFile + "] not found");
                _conn = new SQLiteConnection("Data Source=:memory:;");
                _conn.Open();

                SQLiteCommand command = new SQLiteCommand(_conn);
                command.CommandText = File.ReadAllText(model.SchemaFile);
                command.ExecuteNonQuery();
            }
        }

        public override void AnalyseTables(DatabaseModel model)
        {
            foreach (DataRow row in _conn.GetSchema("Tables").Rows) {
                string table_name = row["TABLE_NAME"] as string;
                if (table_name == "sqlite_sequence")
                    continue;
                model.AddTable(null, table_name);
            }

            DataTable dt = _conn.GetSchema("TableColumns");
            Debug.WriteLine(string.Join(",", dt.Columns.Cast<DataColumn>().Select(_ => _.ColumnName)));
            foreach (DataRow row in dt.Rows) {
                Debug.WriteLine(string.Join(",", row.ItemArray));
                var table = model.Tables.FirstOrDefault(t => t.Name == row["TABLE_NAME"] as string);
                if (table != null)
                    table.Child_Columns.Add(MapColumnInfo(row, false));
                else
                    Debug.Print("unknown table [" + row["TABLE_NAME"] as string + "] for colum [" + row["COLUMN_NAME"] as string + "]");
            }
        }

        public override void AnalyseViews(DatabaseModel model)
        {
            foreach (DataRow row in _conn.GetSchema("Views").Rows) {
                string view_name = row["TABLE_NAME"] as string;
                model.AddView(null, view_name, true);
            }

            DataTable dt = _conn.GetSchema("ViewColumns");
            Debug.WriteLine(string.Join(",", dt.Columns.Cast<DataColumn>().Select(_ => _.ColumnName)));
            foreach (DataRow row in dt.Rows) {
                Debug.WriteLine(string.Join(",", row.ItemArray));
                var view = model.Views.FirstOrDefault(v => v.Name == row["VIEW_NAME"] as string);
                if (view != null)
                    view.Child_Columns.Add(MapColumnInfo(row, true));
                else
                    Debug.Print("unknown view [" + row["VIEW_NAME"] as string + "] for colum [" + row["VIEW_COLUMN_NAME"] as string + "]");
            }
        }

        public override void AnalyseForeignKeys(DatabaseModel model)
        {
            //CONSTRAINT_NAME: FK_Hotspot_0
            //TABLE_NAME: Hotspot
            //CONSTRAINT_TYPE: FOREIGN KEY
            //FKEY_FROM_COLUMN: lesson_id
            // *** FKEY_FROM_ORDINAL_POSITION: 0
            //FKEY_TO_TABLE: lesson
            // *** FKEY_TO_COLUMN: id

            DataTable dt = _conn.GetSchema("ForeignKeys");
            foreach (DataRow row in dt.Rows) {
                if (row["CONSTRAINT_TYPE"] as string != "FOREIGN KEY")
                    continue;

                Debug.Print("----------------------");
                foreach (DataColumn i in dt.Columns) {
                    if (row[i.ColumnName] == DBNull.Value)
                        Debug.Print(i.ColumnName + ": <NULL>");
                    else
                        Debug.Print(i.ColumnName + ": " + Convert.ToString(row[i.ColumnName]));
                }

                var constrained_table = model.Tables.First(t => t.Name.ToLowerInvariant() == (row["TABLE_NAME"] as string).ToLowerInvariant());
                var constrained_column = constrained_table.Child_Columns.First(c => c.Name.ToLowerInvariant() == (row["FKEY_FROM_COLUMN"] as string).ToLowerInvariant());

                var referenced_table = model.Tables.First(t => t.Name.ToLowerInvariant() == (row["FKEY_TO_TABLE"] as string).ToLowerInvariant());
                var referenced_column = referenced_table.Child_Columns.First(c => c.Name.ToLowerInvariant() == (row["FKEY_TO_COLUMN"] as string).ToLowerInvariant());

                constrained_column.ReferencedColumnId = referenced_column.Id;
            }
        }

        public override void Disconnect()
        {
            _conn.Close();
        }

        protected override string GetSpecificUsings()
        {
            return @"
using System.Data.SQLite;
using DbType = System.Data.DbType;";
        }

        protected override void GetDbConnectionSpecifics(StringBuilder s)
        {
            s.AppendLine(@"
        /// <summary>Gets or sets the database name</summary>
        public string Database { get; set; }

        /// <summary>Sets the password</summary>
        public string Password { private get; set; }

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        public DbConnection()
        {
            Database = """";
            Password = """";
            Initalize();
        }            

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        /// <param name=""db"">The database</param>
        /// <param name=""pass"">The password</param>
        public DbConnection(string db, string pass)
        {
            Database = db;
            Password = pass;
            Initalize();
        }            

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        /// <param name=""db"">The database</param>
        public DbConnection(string db)
        {
            Database = db;
            Password = """";
            Initalize();
        }
            
        /// <summary>Opens the connection to the database</summary>
        public void Open()
        {
            if (string.IsNullOrEmpty(Password))
                _connection.ConnectionString = ""Data Source="" + Database + "";"";
            else
                _connection.ConnectionString = ""Data Source="" + Database + "";"" + ""Password="" + Password + "";"";
            _connection.Open();
        }

        /// <summary>Clone the DbConnection</summary>
        /// <returns>The cloned DbConnection</returns>
        public DbConnection Clone()
        {
            DbConnection conn = new DbConnection();
            conn.Database = Database;
            conn.Password = Password;
            conn.AutoConnect = _autoConnect;
            conn.CommandTimeout = _commandTimeout;
            return conn;
        }");
        }

        protected override string GetEscapeStart()
        {
            return "\\\"";
        }

        protected override string GetEscapeEnd()
        {
            return "\\\"";
        }

        protected override string GetDbTypeType()
        {
            return "System.Data.DbType";
        }

        protected override string GetCommandType()
        {
            return "SQLiteCommand";
        }

        protected override string GetDataReaderType()
        {
            return "SQLiteDataReader";
        }

        protected override string GetConnectionType()
        {
            return "SQLiteConnection";
        }

        protected override string GetParameterType()
        {
            return "SQLiteParameter";
        }

        protected override string GetTransactionType()
        {
            return "SQLiteTransaction";
        }

        protected override string GetAutoIncrementSqlFunction()
        {
            return "last_insert_rowid()";
        }

        #endregion
    }
}
